SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
--exec p_msprojstartacc 2012,04,-99
alter      procedure p_msprojstartacc (
  @nyear int,
  @nmonth int,
  @filid int
)  as
begin
  set nocount on
  delete tft_msproject where nyear=@nyear and nmonth=@nmonth  
  declare @projectid int,@nfilid int,@subjectid int,@moneyid int
  declare @bmbala  numeric(18,2)
  declare @fbmbala numeric(18,2)
  declare @tmsum   numeric(18,2)
  declare @ftmsum  numeric(18,2)
  declare @local_tab table(projectid integer,
                           subjectid integer,
                           moneyid integer,
                           bmbala numeric(18,2),
                           fbmbala numeric(18,2))

  declare cur_project cursor local for select * from @local_tab
  declare cur_fil cursor for select fi_filid from td_filiale where fi_filid=@filid or @filid =-99

  open cur_fil
  fetch next from  cur_fil
  into  @nfilid;
  while @@fetch_status = 0
  begin
    insert into tft_msproject (projectid,subjectid,moneyid,nyear,nmonth,filid,tmsumdebit,tmsumcredit,tmsum,
      ftmsumdebit,ftmsumcredit,ftmsum)
    select d.projectid,d.subjectid,d.moneyid,@nyear,@nmonth,@nfilid,
      sum(dcdebitamt) tmsumdebit,sum(dccreditamt) tmsumcredit, sum((dcdebitamt-dccreditamt)*sign(abs(m.formertype-2002))) tmsum,
      sum(debitamt) ftmsumdebit,sum(creditamt) ftmsumcredit, sum((debitamt-creditamt)*sign(abs(m.formertype-2002))) ftmsum
    from tfb_voucherdtl d
    inner join tfb_voucher m on m.voucherid=d.voucherid and m.filid=@nfilid
    inner join tfd_subject s on d.subjectid=s.subjectid
    where s.doproject=1
      and d.projectid is not null
      and (d.voucherid<0 and (d.borderno=2 or d.borderno=3))
    group by  d.subjectid,d.projectid,d.moneyid

    delete from @local_tab
    insert into @local_tab
    select d.projectid,d.subjectid,d.moneyid,
      sum((s.isdebit*2-1)*(dcdebitamt+dccreditamt)) bmbala,
      sum((s.isdebit*2-1)*(debitamt+creditamt)) fbmbala
    from tfb_voucherdtl d
    inner join tfb_voucher m on m.voucherid=d.voucherid and m.filid=@nfilid
    inner join tfd_subject s on d.subjectid=s.subjectid
    where s.doproject=1 and d.projectid is not null
        and (d.voucherid<0 and d.borderno=1)
    group by  d.subjectid,d.projectid,d.moneyid


    open  cur_project
    fetch next from cur_project
    into  @projectid,@subjectid,@moneyid,@bmbala,@fbmbala
    while @@fetch_status = 0
      begin
        update tft_msproject set bmbala =@bmbala,fbmbala=@fbmbala
        where projectid = @projectid and subjectid = @subjectid and moneyid=@moneyid and filid=@nfilid
              and nyear=@nyear and nmonth=@nmonth
        fetch next from cur_project
        into  @projectid,@subjectid,@moneyid,@bmbala,@fbmbala
      end
    close  cur_project
    delete from @local_tab

    insert into @local_tab
    select d.projectid,d.subjectid,d.moneyid,
      sum((s.isdebit*2-1)*(dcdebitamt)) tmsum,
      sum((s.isdebit*2-1)*(debitamt)) ftmsum
    from tfb_voucherdtl d
    inner join tfb_voucher m on m.voucherid=d.voucherid and m.filid=@nfilid
    inner join tfd_subject s on d.subjectid=s.subjectid
    where s.doproject=1 and d.projectid is not null
        and (d.voucherid<0 and d.borderno=4)
    group by  d.subjectid,d.projectid,d.moneyid

    open  cur_project
    fetch next from  cur_project
    into  @projectid,@subjectid,@moneyid,@tmsum,@ftmsum
    while @@fetch_status = 0
    begin
        update tft_msproject set tmsum = @tmsum,ftmsum = @ftmsum
        where projectid = @projectid and subjectid = @subjectid and moneyid=@moneyid and filid=@nfilid
              and nyear=@nyear and nmonth=@nmonth
        fetch next from  cur_project
        into  @projectid,@subjectid,@moneyid,@tmsum,@ftmsum
    end
    close  cur_project
    fetch next from  cur_fil
    into  @nfilid;
  end--end filid
  deallocate  cur_project
  close cur_fil;
  deallocate  cur_fil;
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

